package fast;

import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Scanner;

/**
 * JDBC Class
 * 
 * This class handles connections, reads and writes to a mysql database
 * 
 * @author James Whetherly
 *
 */

// ******************************************************************************
/* CREATE A FILE CALLED: Connector.txt AND REFRENCE IT IN filePath!
 * "jdbc:mysql://c0350a.projects.glos.ac.uk:3306/c0350a","userName", "password"
 *******************************************************************************
 */

public class JDBC {

	private Connection con;

	public static String filePath = "lib/connector.txt";
	public String url = null;
	public String userName = null;
	public String password = null;
	public boolean connected = false;
	public Statement stmt = null;
	public static int FID = 0;
	public static int SID = 0;
	// AutoIncKeyFromResults = the ID for that method once read in
	public int autoIncKeyFromResults = -1;
	java.sql.Date sqlDate = new java.sql.Date(new java.util.Date().getTime());

	/**
	 * Process for selecting the connector file and retrieving the url, userName
	 * and password
	 * 
	 * @throws IOException
	 */
	public void readFileAsString() throws IOException {

		// set up a FileReader/scanner pair
		Scanner lineScan = new Scanner(new FileReader(filePath));

		// only grab the first line of the file
		String line = lineScan.nextLine();

		// Split file into the url, userName and password by looking on token
		// ','
		String[] parsed = line.split(",");
		url = parsed[0];
		userName = parsed[1];
		password = parsed[2];

	}// Close readFileAsString

	/**
	 * Process for creating a connection to the database
	 * 
	 * @return connected
	 * @throws Exception
	 */
	public boolean openDB() throws Exception {

		if (!connected) {
			// read in config details
			readFileAsString();

			// Load Driver
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println(url + " " + userName + " " + password);

			// Establish connection to database
			try {
				con = DriverManager.getConnection(url, userName, password);
				connected = true;
			} catch (SQLException e) {
				System.out.println(e);
			}
		}

		return connected;

	}// close openDB

	/**
	 * Process for entering result data into results and resultsCominded
	 * 
	 * @param data
	 * @throws SQLException
	 */
	public void resultsWrite(String[][] data, int SID, int FID)
			throws SQLException {

		stmt = con.createStatement();
		ResultSet rs;
		
		//build a statement
		StringBuilder statement = new StringBuilder();
		statement.append("INSERT INTO results (slaveIP, data) VALUES");
		
		for(int i = 0; i<data.length; i++){
			statement.append("('");
			statement.append(data[i][0]); //ip
			statement.append("' ,'");
			statement.append(data[i][1]); //data
			statement.append("')");
			
			if(i < data.length-1){
				statement.append(",");
			}
			
		}
		
		
		try {

			// Execute statement and return generated keys
			//stmt.executeUpdate("INSERT INTO results (slaveIP, data) VALUES ('"
			//		+ slaveIP + "' ,'" + data + "')",
			
			stmt.executeUpdate(statement.toString(),
					Statement.RETURN_GENERATED_KEYS);

			// Create a results set and call getGeneratedkeys() for stmt.
			rs = stmt.getGeneratedKeys();
		
			//generate a statment for the n many generated keys
			StringBuilder statement2 = new StringBuilder();
			statement2.append("INSERT INTO resultsCombined (SID, FID, RID) VALUES");
			
			while (rs.next()) {
				autoIncKeyFromResults = rs.getInt(1);
				statement2.append("('");
				statement2.append(SID);
				statement2.append("' ,'");
				statement2.append(FID);
				statement2.append("' ,'");
				statement2.append(autoIncKeyFromResults);
				statement2.append("')");
				statement2.append(",");
				
			}
			//remove a final trailing comma
			String s = statement2.substring(0, statement2.length()-1);
			
			stmt.executeUpdate(s);

		} finally {
			stmt.close();
		}
	}// Close resultWrite

	/**
	 * Process for setting the file path ID
	 * 
	 * @param filePathFH
	 * @return FID
	 * @throws SQLException
	 */
	public int setFilePathID(String filePathFH) throws SQLException {

		int findFID = 0;
		stmt = con.createStatement();
		ResultSet rs;
		ResultSet rs2;

		try {

			// First check if the filePath all ready exists in the table file
			PreparedStatement stmt = con
					.prepareStatement("SELECT FID FROM file "
							+ "where filePath = '" + filePathFH + "'");

			rs = stmt.executeQuery();
			if (rs.next()) {
				// If the filePath does exist return the FID
				int numColumns = rs.getMetaData().getColumnCount();

				for (int i = 1; i <= numColumns; i++) {
					findFID = Integer.valueOf(rs.getObject(i).toString());
				}
				FID = findFID;

			} else {
				// If the filePath does NOT exist create a new entry in the
				// table

				// Execute statement and return generated keys
				stmt.executeUpdate(
						"INSERT INTO file (filePath, date) VALUES ('"
								+ filePathFH + "', '" + sqlDate + "')",
						Statement.RETURN_GENERATED_KEYS);

				System.out
						.println("INSERT INTO file (filePath, date) VALUES ('"
								+ filePathFH + "', '" + sqlDate + "')");

				// Create a results set and call getGeneratedkeys()
				rs2 = stmt.getGeneratedKeys();
				if (rs2.next()) {
					autoIncKeyFromResults = rs2.getInt(1);

					FID = autoIncKeyFromResults;

				} else {
					System.out.println("Error in retriving Key");
				}
			}
		} finally {
			stmt.close();
		}
		System.out.println("FID = " + FID);
		return FID;

	}// Close setFilePathID

	/**
	 * Process for setting the searchID
	 * 
	 * @return SID
	 * @throws SQLException
	 */
	public int setSearchStringID(String searchString) throws SQLException {
		int findSID = 0;

		stmt = con.createStatement();
		ResultSet rs;
		ResultSet rs2;

		try {

			// First check if the filePath all ready exists in the table file
			PreparedStatement stmt = con
					.prepareStatement("SELECT SID FROM searchString "
							+ "where searchString = '" + searchString + "'");

			rs = stmt.executeQuery();
			if (rs.next()) {
				// If the filePath does exist return the FID
				int numColumns = rs.getMetaData().getColumnCount();

				for (int i = 1; i <= numColumns; i++) {
					findSID = Integer.valueOf(rs.getObject(i).toString());
				}

				// Set the fileID to findFID
				SID = findSID;

			} else {
				// If the filePath does NOT exist create a new entry in the
				// table
				// Execute statement and return generated keys
				stmt.executeUpdate(
						"INSERT INTO searchString (searchString, date) VALUES ('"
								+ searchString + "', '" + sqlDate + "')",
						Statement.RETURN_GENERATED_KEYS);

				// Create a results set and call getGeneratedkeys()
				rs2 = stmt.getGeneratedKeys();
				if (rs2.next()) {
					autoIncKeyFromResults = rs2.getInt(1);
					SID = autoIncKeyFromResults;
				} else {
					System.out.println("Error in retriving Key");
				}
			}
		} finally {
			stmt.close();
		}
		System.out.println(SID);
		return SID;
	}// Close setSearchStringID

	/**
	 * Process for closing the connection to a database
	 * 
	 * @return true
	 * @throws SQLException
	 */
	public boolean closeDB() throws SQLException {
		con.close();
		connected = false;
		return true;
	}
}
